Skip Headers

Oracle® Transparent Gateway for DRDA Installation and User's Guide 10g Release 2 (10.2) for UNIX Part Number B16217-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 12 Using the Oracle Transparent Gateway for DRDA

Using the Oracle Transparent Gateway for DRDA involves connecting to the corresponding gateway system and the remote DRDA database associated with the gateway. It is important to understand how to process and use database links. Database links are discussed in detail in the Oracle Database Reference. Read the database link information in that guide to understand database link processing. Then proceed to read this chapter to understand how to set up a database link to a remote DRDA database.

This chapter contains the following sections:

- Processing a Database Link

- Accessing the Gateway

- Accessing AS/400 File Members

- Using the Synonym Feature

- Performing Distributed Queries

- Read-Only Gateway

- Replicating in a Heterogeneous Environment

- CopyingData from Oracle Database 10g to DRDA Server

- Copying Data from DRDA Server to Oracle Database 10g Server

- Tracing SQL Statements

## Processing a Database Link

The database and application administrators of a distributed database system are responsible for managing the database links that define paths to the DRDA database. The tasks are as follows:

- Creating Database Links

- Dropping Database Links

- Examining Available Database Links

- Limiting the Number of Active Database Links

### Creating Database Links

To create a database link and define a path to a remote database, use the `CREATE` `DATABASE LINK` statement. The `CONNECT TO` clause specifies the remote user ID and password to use when creating a session in the remote database. The `USING` clause points to a tnsnames.ora connect descriptor.

Note: If you do not specify a user ID and a password in the `CONNECT TO` clause, then the Oracle Database user ID and password are used.

See Also: Chapter 14, "Security Considerations"for more information.

The following example creates a database link to access information in the DRDA Server database:

    CREATE PUBLIC DATABASE LINK dblink
    CONNECT TO userid IDENTIFIED BY password
    USING 'tns_name_entry';

where:

`dblink` is the complete database link name.

`user id` is the user ID used to establish a session in the remote database. This user ID must be a valid DRDA Server user ID. It must be authorized to any table or file on the DRDA Server that is referenced in the SQL commands. The user ID must be lesser than eight characters.

`password` is the password used to establish a session in the remote database. This password must be a valid DRDA Server password. The password must be lesser than eight characters.

`tns_name_entry` specifies the Oracle Net TNS connect descriptor used to identify the gateway.

Guidelines for Database Links

Database links are active for the duration of a gateway session. If you want to close a database link during a session, then use the `ALTER` session statement.

### Dropping Database Links

You can drop a database link with the `DROP DATABASE LINK` statement. For example, to drop the public database link named `DBLINK,` use the statement:

    DROP PUBLIC DATABASE LINK dblink;

Note: A database link should not be dropped if it is required to resolve an in-doubt distributed transaction. Refer to Oracle Database Administrator's Guide for additional information about dropping database links.

See Also: Oracle Database Administrator's Guide for additional information about dropping database links

### Examining Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database. The `USER_DB_LINKS` data dictionary view shows the defined database links. The `ALL_DB_LINKS` data dictionary views show all accessible (public and private) database links.

### Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases with the parameter `OPEN_LINKS`. This parameter controls the number of remote connections that any single user process can concurrently use with a single SQL statement. Refer to Oracle Database Reference for additional information about limiting the number of active database links.

## Accessing the Gateway

To access the gateway, complete the following steps on the Oracle integrating server:

1. Step 1: Login to the Oracle Integrating Server

2. Step 2: Creating a database Link to the DRDA Database

3. Step 3: Retrieve data from the DRDA Database

Step 1: Login to the Oracle Integrating Server

Log in to the Oracle Integrating Server to access the gateway

Step 2: Creating a database Link to the DRDA Database

For example, use:

    CREATE PUBLIC DATABASE LINK DRDA
    CONNECT TO ORADRDA IDENTIFIED BY oracle_pw
    USING 'tns_name_entry'

Step 3: Retrieve data from the DRDA Database

This query fetches the `TABLE` file in the library `SECURE`, using the name ORACLE as the DRDA Server user profile. The `ORACLE` user profile must have the appropriate privileges on the DRDA Server to access the `SECURE.TABLE` files:

    SELECT * FROM SECURE.TABLE@DRDA

The following is an example of the error messages that are displayed if insufficicent privileges are displayed:

    ORA-1031:   insufficient privileges
    TG4DRDA V10.2.0.1.0 grc=0, drc=-777 (83TC,0000), errp=ARIXO,
    sqlcode=-551, sqlstate=42501, errd=FFFFFF9C,0,0,0,0,0
    errmc=USER SELECT SECURE.TABLE

## Accessing AS/400 File Members

Nothing specific to DRDA or to the gateway controls the access to AS/400 files and file members. However, DB2/400 uses a naming convention that implies that the file member name is the same as the name of the file being addressed. For example, accessing `schema.table` implies that table is the file name and also that table is the file member name being accessed.

To access file members with names that differ from the associated file name, you must create a view within the file so that DB2/400 can reference the correct file member.

One method for creating this view involves issuing the console command `Create Logical File` (`CRTLF`). This action creates a logical association between the file name and the file member name.

See Also: For additional information, refer to the AS/400 Command documentation or to the DB2/400 SQL reference document.

## Using the Synonym Feature

You can provide complete data, location, and network transparency by using the synonym feature of Oracle Database. When a synonym is defined, the user need not know the underlying table or network protocol being used. A synonym can be public, which means it is available to all Oracle users. A synonym can also be defined as private, available only to the user who created it. Refer to Oracle Database Reference for details on the synonym feature.

The following statement creates a system-wide synonym for the `EMP` file in the DRDA Server with ownership of `ORACLE`:

    CREATE PUBLIC SYNONYM EMP FOR ORACLE.EMP@DRDA

## Performing Distributed Queries

The Oracle Transparent gateway technology enables the execution of distributed queries that join Oracle Database and DRDA Servers and any other data store for which Oracle provides a gateway. These complex operations can be completely transparent to the users requesting the data.

The distributed query optimizer (DQO) capability can provide better performance of distributed queries. Statistical data regarding tables from DRDA Server is retrieved and passed to the Oracle Integrating Server. The DQO capability is enabled or disabled by the DRDA_OPTIMIZE_QUERY parameter. Refer to "DRDA_OPTIMIZE_QUERY" for more information.

The following example joins data between an Oracle Database, DB2/OS390, and a DRDA Server:

    SELECT o.custname, p.projno, e.ename, sum(e.rate*p.hours)
    FROM orders@DB2 o, EMP@ORACLE7 e, projects@DRDA p
    WHERE o.projno = p.projno
    AND p.empno = e.empno
    GROUP BY o.custname, p.projno, e.ename

A combination of views and synonyms, using the following SQL statements, keeps the process of distributed queries transparent to the user:

    CREATE SYNONYM orders for orders@DB2;
    CREATE SYNONYM PROJECTS for PROJECTS@DRDA;
    CREATE VIEW details (custname,projno,ename,spend)
    AS
    SELECT o.custname, p.projno, e.ename, sum(e.rate*p.hours)
    FROM orders o, EMP e, projects p
    WHERE o.projno = p.projno
    AND p.empno = e.empno
    GROUP BY o.custname, p.projno, e.ename

The following SQL statement retrieves information from these three data stores in one command:

    SELECT * FROM DETAILS;

The results of this command are:

    CUSTNAME   PROJNO     ENAME      SPEND
    ---------  ---------  ---------  ---------
    ABC Co.    1         Jones      400
    ABC Co.    1         Smith      180
    XYZ Inc.   2         Jones      400
    XYZ Inc.   2         Smith      180

### Two-Phase Commit Processing

To fully participate in a two-phase commit transaction, a server must support the `PREPARE TRANSACTION` statement. The `PREPARE TRANSACTION` statement ensures that all participating databases are prepared to `COMMIT` or to `ROLLBACK` a specific unit of work.

Oracle Database supports the `PREPARE TRANSACTION` statement. Any number of Oracle DatabaseS can participate in a distributed two-phase commit transaction. The `PREPARE TRANSACTION` statement is performed automatically when a `COMMIT` is issued explicitly by an application or implicitly at the normal end of the application.

The gateway does not support the `PREPARE TRANSACTION` statement. This limits the two-phase commit protocol when the gateway participates in a distributed transaction. The gateway becomes the commit focal point site of a distributed transaction. The gateway is configured as commit/confirm, so it is always the commit point site, regardless of the commit point strength setting. The gateway commits the unit of work after verifying that all Oracle Databases in the transaction have successfully committed their work. The gateway must coordinate the distributed transaction, so only one gateway can participate in an two-phase commit transaction.

Two-phase commit transactions are recorded in the `ORADRDA.ORACLE2PC` table, which is created during installation. This table is created when the o2pc.sql script is run. The owner of this table also owns the package. Refer to "DRDA Gateway Package Considerations" for more information.

### Distributed DRDA Transactions

Because the `ORACLE2PC` table is used to record the status of a gateway transaction, this table must be in at the database where the DRDA update takes place. Therefore, all updates that take place over the gateway must be local to the IBM database.

Note: Updates to the `ORACLE2PC` table cannot be part of an IBM distributed transaction.

For additional information about the two-phase commit process, refer to Oracle Database Administrator's Guide.

## Read-Only Gateway

The read-only option can provide improved performance and security. This improved performance depends on your configuration and parameter selections. A Gateway Initialization Parameter, `DRDA_READ_ONLY`, controls whether the gateway is enabled in this mode.

If you enable the read-only option, then only queries (`SELECT` statements) are allowed by the gateway. The capabilities that control whether updates are allowed by the gateway are disabled. These capabilities include `INSERT`, `UPDATE`, `DELETE` and stored-procedure support (pass-through SQL and DB2 stored procedures). Statements attempting to modify records on the DRDA Server are rejected.

Oracle recommends that you should not routinely switch between settings of the `DRDA_READ_ONLY` parameter. If you need both the update and `DRDA_READ_ONLY` functionality, then you should create two separate instances of the gateway with different read-only settings.

## Replicating in a Heterogeneous Environment

Oracle Transparent Gateway for DRDA provides a number of options for replicating Oracle and non-Oracle data throughout the enterprise.

Oracle Database 10g Triggers

When updates are made to Oracle Database, synchronous copies of Oracle and non-Oracle data can be maintained automatically by using Oracle Database 10g triggers.

Oracle Snapshots

Oracle Transparent Gateway for DRDA can use the Oracle snapshot feature to automatically replicate non-Oracle data into Oracle Database. The complete refresh capability of Oracle snapshot can be used to propagate a complete copy or a subset of the non-Oracle data into Oracle Database at user-defined intervals.

## Copying Data from Oracle Database 10g to DRDA Server

The `COPY` command enables you to copy data from Oracle Database to a DRDA Server database. The Oracle SQL command `INSERT` is not supported. If you use the `INSERT` command:

    INSERT INTO DRDA_table SELECT * FROM local_table

then the following message is displayed:

    ORA-2025: All tables in the SQL statement must be at the remote database

To copy data from your Oracle database to the DRDA Server, use:

    COPY FROM username/password@connect_identifier -
    INSERT destination_table -
    USING query

For example, to select all rows from the local Oracle ```EMP` table, insert them into the `EMP` table on the DRDA Server, and commit the transaction, use:

    COPY FROM scott/tiger@ORACLE -
    INSERT scott.EMP@DRDA -
    USING SELECT * FROM EMP

The SQL*Plus `COPY` command supports `APPEND`, `CREATE`, `INSERT`, and `REPLACE` commands. However, `INSERT` is the only command supported when copying to the DRDA Server. For more information about the `COPY` command, refer to SQL*Plus User's Guide and Reference.

## Copying Data from DRDA Server to Oracle Database 10g Server

The `CREATE TABLE` command enables you to copy data from a DRDA Server to Oracle Database. To create a table on your Oracle database and to insert rows from a DRDA Server table, use:

    CREATE TABLE table_name
    AS query

The following example creates the table `EMP` in your local Oracle Database and inserts the rows from the `EMP` table on the DRDA Server:

    CREATE TABLE EMP
    AS SELECT * FROM scott.EMP@DRDA

Alternatively, you can use the SQL*Plus `COPY` command to copy data from a DRDA Server to Oracle Database. For more information about the `COPY` command, refer to SQL*Plus User's Guide and Reference.

## Tracing SQL Statements

SQL statements issued through the gateway can be changed before reaching the DRDA database. These changes are made to make the format acceptable to the gateway or to make Oracle SQL compatible with DRDA Server SQL. Oracle Integrating Server and the gateway can change the statements depending on the situation.

For various reasons, you might need to assess whether the gateway has altered the statement correctly or whether the statement could be rewritten to improve performance. SQL tracing is a feature that allows you to view the changes made to a SQL statement by the Oracle Integrating Server or the gateway.

SQL tracing reduces gateway performance. Use tracing only while testing and debugging your application. Do not enable SQL tracing when the application is running in a production environment. For more information about enabling SQL tracing, refer to the section on "SQL Tracing and the Gateway" in Chapter 16, "Error Messages, Diagnosis, and Reporting".